# coding=utf-8

# path to database goes here
#path=r'D:\mjdata\February 2016 Traceability\biotrackthc_public_records_20151231.db'
#path=r'C:\Users\Keaton Miller\Dropbox\research\marijuana\data\2016_07\biotrackthc_public_records_20160731.db'
path=r'/mnt/sdb1/Dropbox/research/marijuana/data/2017_06/biotrackthc_public_records_20170630.db'

#path=r'E:\Keaton Miller\Dropbox\research\marijuana\data\2017_06\biotrackthc_public_records_20170630.db'

# imports
import sqlite3
import os
import csv
import string
import re
from datetime import datetime, date

# calculate new path for cleaned files
dir = os.path.dirname(path)
#clean_dir = dir + '\\clean\\'
clean_dir = dir + '/clean/'

# limit constant, used for development
#limit = "LIMIT 1000"
limit = ""

# create the path if it doesn't exist
if not os.path.exists(clean_dir):
    os.makedirs(clean_dir)

# Set up connection
conn = sqlite3.connect(path, detect_types=sqlite3.PARSE_DECLTYPES)

# set the text factory to bytes, to ensure that we don't end up with bad unicode types
#conn.text_factory = bytes
#conn.text_factory = lambda x: str(x, 'latin-1')

c = conn.cursor()

# define function for writing output from SQL
def writeSQL(cursor, filename):
    global clean_dir
    with open(clean_dir + filename + '.csv', 'w', newline='', encoding='utf8') as csvfile:
        # grab the list of fields
        fields = [tup[0] for tup in cursor.description]
        writer = csv.writer(csvfile, quoting=csv.QUOTE_NONNUMERIC)
        writer.writerow(fields)
        writer.writerows(cursor)

# define function for sanitizing fields
def sanitize(field):
    return "trim(replace(replace(" + field + ", X'0D', ''), X'0A', '')) AS " + field[field.find('.')+1:]

# define function for translating unixtime
def unixtime(field):
    return "datetime(" + field +", 'unixepoch') AS " + field

# define function for executing query and outputting the results
def querySave(query, cursor, filename):
    print(query)
    cursor.execute(query)
    print("Query complete. Saving...")
    writeSQL(cursor, filename)

# define function for grabbing everything
def getAll(tablename, cursor):
    global limit
    query = "SELECT * FROM " + tablename + " " + limit
    if tablename.find('biotrackthc') > -1:
        tablename = tablename[tablename.find('_')+1:]
    querySave(query, cursor, tablename)

# define function for creating an index on a column

def makeIndex(index, tablename, cursor, uniqueFlag=0):
    """ Creates an index on tablename via cursor, named by stripping the index input."""
    # if the unique flag is set, include UNIQUE in our command
    unique = " "
    if uniqueFlag == 1:
        unique = " UNIQUE "
    print("Creating"+unique+"index on " + tablename + " with columns " + index + "...")
    # if this is the first time we have called this function,
    # we need to set up a regex pattern to strip all non-alphanumeric characters
    # from our index variable
    if not hasattr(makeIndex, "pattern"):
        makeIndex.pattern = re.compile('[\W_]+')
        
    # Use our pattern to make a name for our index
    indexName = makeIndex.pattern.sub('', tablename+index)

    # drop our index if it already exists
    cursor.execute("DROP INDEX IF EXISTS " + indexName)

    # now create our index

        
    cursor.execute("CREATE " + unique + " INDEX IF NOT EXISTS " + indexName + " ON " + tablename + "(" + index + ")" )
    


# Each table in the database requires different processing.
# Go one-by-one through the tables, add appropriate indices,
# select the relevant columns, clean them when required,
# and output them to a CSV in the clean_dir

# BIOTRACKTHC_INVENTORYADJUSTMENTS
query = "SELECT id, inventoryid, oldweight, newweight, " + \
	"difference, sessiontime, transactionid, orgid, location, atype, inventory_time, " + \
	sanitize('reason') + \
	" FROM biotrackthc_inventoryadjustments " + \
	limit
querySave(query, c, 'inventoryadjustments')


# BIOTRACKTHC_DISPENSING
makeIndex('orgid', 'biotrackthc_dispensing', c)
makeIndex('location', 'biotrackthc_dispensing', c)
makeIndex('transactionid', 'biotrackthc_dispensing', c)
query = "SELECT id, orgid, location, sessiontime" + \
         ", transactionid, itemnumber, " + \
         "inventoryid, inventorytype, weight, usableweight, abs(price) AS price, deleted, refunded, transactionid_original " + \
         "FROM biotrackthc_dispensing " + \
         limit
querySave(query, c, 'dispensing')

# BIOTRACKTHC_GROWROOMS
makeIndex('orgid', 'biotrackthc_growrooms', c)
makeIndex('location', 'biotrackthc_growrooms', c)
makeIndex('roomid', 'biotrackthc_growrooms', c)
query = "SELECT id, orgid, location, roomid, " + \
         sanitize('name') + \
         ", deleted, transactionid, transactionid_original " + \
         "FROM biotrackthc_growrooms " + \
         limit
querySave(query, c, 'growrooms')

# BIOTRACKTHC_INVENTORY

# there is a single duplicate ID. get rid of it
c.execute("DELETE FROM biotrackthc_inventory WHERE id == '0214523902408153' AND idserial == '3236433'")

# there are some diacrticals in one particular inventory ID for Creme Brulee
c.execute("UPDATE biotrackthc_inventory SET strain = 'Creme Brulee' WHERE id == '6033558320000160'")

# diacriticals in creme brulee
c.execute("UPDATE biotrackthc_inventory SET strain = 'Creme Brulee' WHERE strain LIKE 'Cr%' AND strain LIKE '%me Brulee';")

# diacriticals in Sour Pina Colada
c.execute("UPDATE biotrackthc_inventory SET strain = 'Sour Pina Colada' WHERE strain LIKE '%Sour Pi%' AND strain LIKE '%Colada%';")

# productname stuff
c.execute("UPDATE biotrackthc_inventory SET productname = 'TheBaker Sample' WHERE productname LIKE 'TheBaker%';")

makeIndex('id', 'biotrackthc_inventory', c, 1)
makeIndex('orgid', 'biotrackthc_inventory', c)
makeIndex('location', 'biotrackthc_inventory', c)
makeIndex('sample_id', 'biotrackthc_inventory', c)
query = "SELECT id, sessiontime, " + \
         sanitize('strain') + \
         ", weight, transactionid, plantid, location, remainingweight" + \
         ", requiresweighing, parentid, inventorytype, wet, seized, orgid, deleted, plantarray, usableweight, removescheduled, removescheduletime, inventoryparentid, "  + \
         sanitize('productname')  + \
         ", currentroom, idserial"  + \
         ", inventorystatus, inventorystatustime, inventoryparentidpct, sample_id, source_id, transactionid_original, recalled "  + \
         "FROM biotrackthc_inventory "  + \
         limit
querySave(query, c, 'inventory')

# BIOTRACKTHC_INVENTORY_FIFTEEN
# there are some diacrticals in one particular inventory ID for Creme Brulee
c.execute("UPDATE biotrackthc_inventory_fifteen SET strain = 'Creme Brulee' WHERE inventoryid == '6033558320000160'")

# diacriticals in creme brulee
c.execute("UPDATE biotrackthc_inventory_fifteen SET strain = 'Creme Brulee' WHERE strain LIKE 'Cr%' AND strain LIKE '%me Brulee';")

# diacriticals in Sour Pina Colada
c.execute("UPDATE biotrackthc_inventory_fifteen SET strain = 'Sour Pina Colada' WHERE strain LIKE '%Sour Pi%' AND strain LIKE '%Colada%';")

query = "SELECT id, inventoryid, orgid, location, deleted, sessiontime, quantity, inventorytype, "  + \
        sanitize('strain') + \
        " FROM biotrackthc_inventory_fifteen "  + \
        limit
querySave(query, c, 'inventory_fifteen')

# BIOTRACKTHC_INVENTORY_STATISTICS
getAll('biotrackthc_inventory_statistics', c)

# BIOTRACKTHC_INVENTORY_QA_FAIL_EXEMPT
getAll('biotrackthc_inventory_qa_fail_exempt', c)



# BIOTRACKTHC_INVENTORYADJUSTMENTS
# This is where someone used some bad unicode. fix it.
print("Fixing bad unicode on INVENTORYADJUSTMENTS")
query = 'UPDATE biotrackthc_inventoryadjustments SET reason = "moved 27.5 back to this lot from 6033 4959 0000 0860 Purple Kush (purple kush 1 gram)10.0 ... 1g 6033 4959 0000 0861 Purple Kush (purple kush 3.5 grams)5.0 ... 3.50g because the store cancelled their order" WHERE reason LIKE "%moved 27.5 back%"'
c.execute(query)
query = 'UPDATE biotrackthc_inventoryadjustments SET reason = ".5g returned from each (total 10g): 9856 6837 5184 3061 7950 7935 1299 1459 4877 8356 9570 8130 1662 9514 3129 8210 0307 1430 6662 1358 4690 8697 2164 4227 7919 7395 0302 5116 2313 2476 7020 5546 2236 1250 8521 7748 9615 5871 4513 1927 1901 6658 2143 3432 5156 8124 9798 9272 1514 3085 9914 1675 6445 7339 9597 9985 3959 4926 9875 7715 1220 8947 2849 7222 3178 3654 6722 0927 8254 7372 0695 3929 4570 5377 1809 8844 0728 4785 2862 2294" where reason LIKE "%5g returned from each (total 10g): 9856%"' 
c.execute(query)
query = "SELECT id, inventoryid, oldweight, newweight, difference, sessiontime, transactionid, orgid, " + \
        sanitize('reason') + \
        ", atype, location, transactionid_original, inventory_time " + \
        " FROM biotrackthc_inventoryadjustments " + \
        limit
querySave(query, c, 'inventoryadjustments')



# BIOTRACKTHC_INVENTORYCOMBINATIONS
makeIndex('newid', 'biotrackthc_inventorycombinations', c, 1)
getAll('biotrackthc_inventorycombinations', c)

# BIOTRACKTHC_INVENTORYCONVERSIONS
makeIndex('inventoryid', 'biotrackthc_inventoryconversions', c)
makeIndex('childid', 'biotrackthc_inventoryconversions', c)
query = "SELECT id, inventoryid, oldweight, newweight, difference, sessiontime, " + \
        "transactionid, childid, childweight, requiresweighing, orgid, inventoryarray, inventorytype, location, transactionid_original, inventorytype_parent " + \
        "FROM biotrackthc_inventoryconversions " + limit
querySave(query, c, 'inventoryconversions')

# BIOTRACKTHC_INVENTORYROOMS
makeIndex('id', 'biotrackthc_inventoryrooms', c, 1)
makeIndex('orgid', 'biotrackthc_inventoryrooms', c)
makeIndex('location', 'biotrackthc_inventoryrooms', c)
query = "SELECT id, orgid, location, roomid, " + sanitize('name') + \
        ", deleted, quarantine, transactionid, transactionid_original " + \
        "FROM biotrackthc_inventoryrooms " + limit
querySave(query, c, 'inventoryrooms')

# BIOTRACKTHC_INVENTORYSAMPLES
makeIndex('orgid', 'biotrackthc_inventorysamples', c)
makeIndex('inventoryid', 'biotrackthc_inventorysamples', c)
makeIndex('id', 'biotrackthc_inventorysamples', c, 1)
getAll('biotrackthc_inventorysamples', c)

# BIOTRACKTHC_INVENTORYTRANSFERS
c.execute("UPDATE biotrackthc_inventorytransfers SET strain = 'Sour Pina Colada' WHERE strain LIKE '%Sour Pi%' AND strain LIKE '%Colada%';")
c.execute("UPDATE biotrackthc_inventorytransfers SET description = 'TheBaker Sample' WHERE description LIKE 'TheBaker%';")

makeIndex('inventoryid', 'biotrackthc_inventorytransfers', c)
makeIndex('orgid', 'biotrackthc_inventorytransfers', c)
makeIndex('location', 'biotrackthc_inventorytransfers', c)
makeIndex('manifestid', 'biotrackthc_inventorytransfers', c)
makeIndex('inbound_location', 'biotrackthc_inventorytransfers', c)
makeIndex('inbound_orgid', 'biotrackthc_inventorytransfers', c)
query = "SELECT id, inventoryid, " + sanitize('strain') + ", weight, transactionid" + \
        ", location, direction, requiresweighing, transfertype, orgid, parentid" + \
        ", inventorytype, usableweight, outbound_license, inbound_license, " + \
        sanitize('description') + ", abs(saleprice) as saleprice, manifestid" + \
        ", manifest_stop, received, receiveweight, deleted, unitprice, is_refund" + \
        ", refund_amount, inbound_location, transactionid_original, inbound_orgid, transactionid_original_inbound " + \
        "FROM biotrackthc_inventorytransfers " + limit
querySave(query, c, 'inventorytransfers')
              

# BIOTRACKTHC_LABRESULTS_FOREIGN_MATTER
getAll('biotrackthc_labresults_foreign_matter', c)

# BIOTRACKTHC_LABRESULTS_MICRO_SCREENING
getAll('biotrackthc_labresults_micro_screening', c)

# BIOTRACKTHC_LABRESULTS_MOISTURE_CONTENT
getAll('biotrackthc_labresults_moisture_content', c)

# BIOTRACKTHC_LABRESULTS_POTENCY_ANALYSIS
getAll('biotrackthc_labresults_potency_analysis', c)

# BIOTRACKTHC_LABRESULTS_SAMPLES
c.execute("UPDATE biotrackthc_labresults_samples SET strain = 'Sour Pina Colada' WHERE strain LIKE '%Sour Pi%' AND strain LIKE '%Colada%';")
query = "SELECT id, inventoryid, quantity, sessiontime, inventorytype, " + \
        sanitize('strain') + ", " + sanitize('product_name') + \
        ", deleted, result, lab_license, sample_use, location, orgid, transactionid" + \
        ", parentid, received, received_quantity, transactionid_original, sample_amount_used" + \
        ", sample_amount_destroyed, sample_amount_other, other_sample_id, inventoryparentid " + \
        "FROM biotrackthc_labresults_samples " + limit
querySave(query, c, 'labresults_samples')

# BIOTRACKTHC_LABRESULTS_SOLVENT_SCREENING
getAll('biotrackthc_labresults_solvent_screening', c)

# BIOTRACKTHC_LOCATIONS
# TODO: come back and sanitize ALL of these fields
makeIndex('id', 'biotrackthc_locations', c,1)
makeIndex('orgid', 'biotrackthc_locations', c)
print("Fixing location 1260...")
query = "UPDATE biotrackthc_locations SET loclatitude = 47.228363, loclongitude = -122.479238 WHERE id == 1260"
c.execute(query)
print("Fixing location 2356...")
c.execute("UPDATE biotrackthc_locations SET address1 = '930 1/2 VALLEY MALL PKWY' WHERE id == '2356';")
getAll('biotrackthc_locations', c)

# LATLONG_COMBOS
query = "select a.orgid as source_org, a.id as source_loc, b.orgid as dest_org, " +\
        "b.id as dest_loc, a.loclatitude as source_lat, a.loclongitude as source_long, " +\
        "b.loclatitude as dest_lat, b.loclongitude as dest_long " +\
        "from biotrackthc_locations as a join biotrackthc_locations as b"
querySave(query, c, 'latlong_combos')


# BIOTRACKTHC_LOCATIONS_LABS
getAll('biotrackthc_locations_labs', c)

# BIOTRACKTHC_ORGANIZATIONS
makeIndex('orgid', 'biotrackthc_organizations', c,1)
getAll('biotrackthc_organizations', c)

# BIOTRACKTHC_ORGANIZATIONS_LABS
getAll('biotrackthc_organizations_labs', c)

# BIOTRACKTHC_PLANTDERIVATIVES
makeIndex('plantid', 'biotrackthc_plantderivatives', c)
makeIndex('inventoryid', 'biotrackthc_plantderivatives', c)
getAll('biotrackthc_plantderivatives', c)

# BIOTRACKTHC_PLANTS
# diacriticals in creme brulee
c.execute("UPDATE biotrackthc_plants SET strain = 'Creme Brulee' WHERE strain LIKE 'Cr%' AND strain LIKE '%me Brulee';")

makeIndex('id', 'biotrackthc_plants', c, 1)
query = "SELECT id, " + sanitize('strain') + ", sessiontime, transactionid, seed, " + \
        "parentid, deleted, deletetime, location, state, cloneconverted, seized, " + \
        "orgid, harvestscheduled, harvestscheduletime, harvestcollect, " +\
        "curecollect, room, mother, removescheduled, removescheduletime, " + \
        sanitize('removereason') + ", idserial, modified, transactionid_original " + \
        "FROM biotrackthc_plants " + limit
querySave(query, c, 'plants')

# BIOTRACKTHC_PLANTSLOG
# diacriticals in creme brulee
c.execute("UPDATE biotrackthc_plantslog SET strain = 'Creme Brulee' WHERE strain LIKE 'Cr%' AND strain LIKE '%me Brulee';")
makeIndex('plantid', 'biotrackthc_plantslog',c)
makeIndex('state', 'biotrackthc_plantslog',c)
query = "SELECT id, plantid, " + sanitize('strain') + ", sessiontime, transactionid, " + \
        "seed, inventoryid, parentid, deleted, deletetime, location, state, cloneconverted, " + \
        "seized, orgid, harvestscheduled, harvestscheduletime, created, harvestcollect, " + \
        "curecollect, room, transactionid_original FROM biotrackthc_plantslog " + limit
querySave(query, c, 'plantslog')

# BIOTRACKTHC_TAX_OBLIGATION_REPORTS
getAll('biotrackthc_tax_obligation_reports', c)

# BIOTRACKTHC_UNDO_HISTORY
getAll('biotrackthc_undo_history', c)

########
# Difficult tables!
########

# BIOTRACKTHC_INVENTORYLOG
makeIndex('id', 'biotrackthc_inventorylog', c)
makeIndex('location', 'biotrackthc_inventorylog', c)
makeIndex('orgid', 'biotrackthc_inventorylog', c)

# now that we've indexed all the tables it's time to ANALYZE the table
print("\n\nAll indices added. ANALYZE-ing the database...\n\n")
c.execute("ANALYZE")


#######
# Derived tables!
#######


# Derived table: INVENTORY_TIMESTAMPS
print("Creating inventory_timestamps...")
c.execute("DROP TABLE IF EXISTS inventory_timestamps")
c.execute("CREATE TABLE inventory_timestamps AS SELECT orgid, " + \
          "location, id, MIN(created) as first_seen, MAX(created) as last_seen  FROM biotrackthc_inventorylog GROUP BY 1, 2, 3")
makeIndex('orgid', 'inventory_timestamps', c)
makeIndex('location', 'inventory_timestamps', c)
makeIndex('id', 'inventory_timestamps', c)
c.execute("ANALYZE inventory_timestamps")


# Derived table: latlong_combinations
print("Dumping latlong combos...")
query = "select a.orgid as source_org, a.id as source_loc, b.orgid as dest_org, b.id as dest_loc, " +\
        "a.loclatitude as source_lat, a.loclongitude as source_long, b.loclatitude as dest_lat, b.loclongitude as dest_long " +\
        " from biotrackthc_locations as a join biotrackthc_locations as b "
querySave(query, c, 'latlong_combos')


# Derived table: Producer transfers
print("Dumping Producer transfers")
query = "SELECT a.id, inventoryid, " +sanitize('strain') + ", inventorytype, weight, usableweight, saleprice, unitprice, " +\
        "first_seen as transfer_date, a.location as source_loc, a.inbound_location as dest_loc " +\
        "from biotrackthc_inventorytransfers as a join inventory_timestamps as b on a.inventoryid = b.id and a.inbound_location = b.location " +\
        "where inbound_location in (select id from biotrackthc_locations where processor == 1) " +\
        "and a.location in (select id from biotrackthc_locations where producer == 1) and is_refund == 0 and deleted == 0" # and a.orgid != a.inbound_orgid"
querySave(query,c,'producer_transfers')



# Derived table: potency_summary
print("Creating potency_summary...")
c.execute("DROP TABLE IF EXISTS potency_analysis_cleaned")
query = "create table potency_analysis_cleaned as select sample_id, name, max(value) as value " +\
        " FROM biotrackthc_labresults_potency_analysis WHERE failure == 0 AND lab_provided == 1 " +\
        " group by 1, 2 order by 1,2"
print(query)
c.execute(query)
makeIndex('sample_id', 'potency_analysis_cleaned', c)

c.execute("DROP TABLE IF EXISTS potency_summary")
query = "CREATE TABLE potency_summary AS SELECT samp.inventoryid as sample_id, cast(samp.lab_license AS integer) AS lab_license, a.value AS potency_CBD, b.value AS potency_THC, c.value AS potency_THCA, d.value AS potency_total " +\
        " from (SELECT id as sample_id, lab_license, inventoryid from biotrackthc_labresults_samples) as samp "+\
        " JOIN (SELECT sample_id, value FROM potency_analysis_cleaned WHERE name = 'CBD') as a ON samp.sample_id = a.sample_id "+\
        " JOIN (SELECT sample_id, value FROM potency_analysis_cleaned WHERE name = 'THC') as b ON samp.sample_id = b.sample_id "+\
        " JOIN (SELECT sample_id, value FROM potency_analysis_cleaned WHERE name = 'THCA') as c ON samp.sample_id = c.sample_id "+\
        " JOIN (SELECT sample_id, value FROM potency_analysis_cleaned WHERE name = 'Total') as d ON samp.sample_id = d.sample_id "+\
        " order by sample_id "
print(query)
c.execute(query)
makeIndex('sample_id', 'potency_summary', c, 1)






# Derived table: TRANSFER_RELATIONSHIPS
print("Creating transfer_relationships")
c.execute("DROP TABLE IF EXISTS transfer_relationships")
query = "CREATE TABLE transfer_relationships AS SELECT a.location AS source_location, a.orgid AS source_orgid, " +\
        "a.inbound_location AS dest_location, a.inbound_orgid AS dest_orgid, MIN(b.first_seen) as first_transaction, " +\
        "MAX(b.first_seen) AS last_transaction, count(a.id) as transfer_count " +\
        "FROM biotrackthc_inventorytransfers as a JOIN inventory_timestamps as b ON a.inbound_location = b.location AND a.inbound_orgid = b.orgid AND a.inventoryid = b.id GROUP BY 1, 2, 3, 4"
c.execute(query)
getAll('transfer_relationships', c)

# Derived table: INVENTORY_PLANTS
print("Creating inventory_plants")
c.execute("DROP TABLE IF EXISTS inventory_plants")
# this query modified from http://sqlite.1065341.n5.nabble.com/comma-separated-string-data-td74926.html

query = """
CREATE TABLE inventory_plants AS 
WITH csvrec(i,l,c,r) AS ( 
       SELECT id, 1, plantarray||',', '' FROM biotrackthc_inventory WHERE plantarray <> ""
     UNION ALL 
       SELECT i, 
              instr(c,',') AS vLen, 
              substr(c,instr(c,',')+1) AS vRem, 
              substr(c,1,instr(c,',')-1) AS vCSV 
       FROM csvrec 
       WHERE vLen>0 
     ) 
   SELECT t.id, rt.r as plantid FROM biotrackthc_inventory AS t, csvrec AS rt 
   WHERE t.id=rt.i AND rt.r<>'' 
   ORDER BY t.ID 
"""


c.execute(query)
makeIndex('id', 'inventory_plants', c)
makeIndex('plantid', 'inventory_plants', c)

# Derived table: INVENTORY_PARENTS
print("Creating inventory_parents")
c.execute("DROP TABLE IF EXISTS inventory_parents")

query = """
CREATE TABLE inventory_parents AS
WITH csvrec(i,l,c,r) AS ( 
       SELECT id, 1, parentid||',', '' FROM biotrackthc_inventory WHERE parentid <> ""
     UNION ALL 
       SELECT i, 
              instr(c,',') AS vLen, 
              substr(c,instr(c,',')+1) AS vRem, 
              substr(c,1,instr(c,',')-1) AS vCSV 
       FROM csvrec 
       WHERE vLen>0 
     ) 
   SELECT t.id, rt.r as parentid FROM biotrackthc_inventory AS t, csvrec AS rt 
   WHERE t.id=rt.i AND rt.r<>'' 
   ORDER BY t.ID 
"""

c.execute(query)
makeIndex('id', 'inventory_parents',c)
makeIndex('parentid', 'inventory_parents',c)
c.execute("ANALYZE INVENTORY_PARENTS")

# DERIVED TABLE: INVENTORY_PRODUCER
print("Creating inventory_producer from script")
c.execute("DROP TABLE IF EXISTS inventory_producer")

query = """
CREATE TEMPORARY TABLE tmp_inv_plant_org AS SELECT a.id as inventoryid, b.orgid as plantorgid, sum(1) as plantcount FROM inventory_plants AS a JOIN biotrackthc_plants AS b ON a.plantid = b.id GROUP BY 1,2;

CREATE TEMPORARY TABLE tmp_inv_plant_origin AS select inventoryid, plantorgid from tmp_inv_plant_org group by inventoryid;
UPDATE tmp_inv_plant_origin SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_plant_org GROUP BY 1) where rowcount > 1);

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_plant_origin AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_plant_origin) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_plant_origin
;

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full GROUP BY inventoryid;
UPDATE tmp_inv_origin_from_parent SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full GROUP BY 1) where rowcount > 1);

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full2 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent
;

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent2 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full2 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent2 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full2 GROUP BY 1) where rowcount > 1);

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full3 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent2 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent2) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent2
;


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent3 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full3 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent3 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full3 GROUP BY 1) where rowcount > 1);

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full4 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent3 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent3) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent3
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent4 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full4 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent4 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full4 GROUP BY 1) where rowcount > 1);

CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full5 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent4 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent4) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent4
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent5 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full5 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent5 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full5 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full6 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent5 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent5) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent5
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent6 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full6 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent6 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full6 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full7 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent6 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent6) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent6
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent7 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full7 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent7 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full7 GROUP BY 1) where rowcount > 1);





CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full8 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent7 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent7) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent7
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent8 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full8 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent8 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full8 GROUP BY 1) where rowcount > 1);



CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full9 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent8 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent8) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent8
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent9 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full9 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent9 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full9 GROUP BY 1) where rowcount > 1);



CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full10 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent9 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent9) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent9
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent10 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full10 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent10 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full10 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full11 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent10 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent10) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent10
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent11 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full11 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent11 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full11 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full12 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent11 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent11) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent11
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent12 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full12 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent12 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full12 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full13 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent12 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent12) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent12
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent13 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full13 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent13 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full13 GROUP BY 1) where rowcount > 1);


CREATE TEMPORARY TABLE tmp_inv_origin_from_parent_full14 AS
SELECT a.id AS inventoryid, b.plantorgid AS plantorgid, sum(1) as parentcount
FROM inventory_parents AS a JOIN tmp_inv_origin_from_parent13 AS b on a.parentid = b.inventoryid WHERE a.id NOT IN (SELECT inventoryid FROM tmp_inv_origin_from_parent13) GROUP BY 1,2
UNION SELECT inventoryid, plantorgid, 0 as parentcount FROM tmp_inv_origin_from_parent13
;
CREATE TEMPORARY TABLE tmp_inv_origin_from_parent14 AS
SELECT inventoryid, plantorgid FROM tmp_inv_origin_from_parent_full14 GROUP BY inventoryid;

UPDATE tmp_inv_origin_from_parent14 SET plantorgid = "Mixed" WHERE inventoryid in (SELECT inventoryid from (SELECT inventoryid, count(*) as rowcount FROM tmp_inv_origin_from_parent_full14 GROUP BY 1) where rowcount > 1);

CREATE TABLE inventory_producer AS SELECT inventoryid, plantorgid AS prodorg FROM tmp_inv_origin_from_parent14;

CREATE UNIQUE INDEX inventory_producer_inventoryid ON inventory_producer(inventoryid);

ANALYZE;

"""

c.executescript(query)

# Derived table: INVENTORY_PARENTLOG
print("Creating inventory_parentlot...")

query = """
CREATE TEMPORARY TABLE tmp_inv_parents AS SELECT a.id, b.inventorytype AS type, a.parentid FROM inventory_parents AS a JOIN biotrackthc_inventory AS b on a.id=b.id JOIN biotrackthc_inventory AS c on a.parentid=c.id WHERE b.inventorytype = c.inventorytype;

CREATE TEMPORARY TABLE tmp_inv_parents2 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents AS a JOIN tmp_inv_parents AS b on a.parentid = b.id AND a.type = b.type;

CREATE TEMPORARY TABLE tmp_inv_parents3 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents2 AS a JOIN tmp_inv_parents2 AS b on a.parentid = b.id AND a.type = b.type;

CREATE TEMPORARY TABLE tmp_inv_parents4 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents3 AS a JOIN tmp_inv_parents3 AS b on a.parentid = b.id AND a.type = b.type;

CREATE TEMPORARY TABLE tmp_inv_parents5 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents4 AS a JOIN tmp_inv_parents4 AS b on a.parentid = b.id AND a.type = b.type;

CREATE TEMPORARY TABLE tmp_inv_parentall AS SELECT * from tmp_inv_parents UNION SELECT * from tmp_inv_parents2 UNION SELECT * from tmp_inv_parents3 UNION SELECT * from tmp_inv_parents4;

CREATE TABLE inventory_parentlot AS SELECT a.id, b.id AS parentlot FROM (select id, min(parentserial) as firstparentserial FROM (select a.id, a.parentid, b.idserial AS parentserial from tmp_inv_parentall AS a JOIN biotrackthc_inventory AS b on a.parentid = b.id ORDER BY a.id) GROUP BY id) AS a JOIN biotrackthc_inventory AS b on a.firstparentserial = b.idserial;

CREATE UNIQUE INDEX inventory_parentlot_id ON inventory_parentlot(id);

ANALYZE inventory_parentlot;

"""
#c.executescript(query)

# Alternative INVENTORY_PARENTLOT table
query = """
CREATE TEMPORARY TABLE tmp_inv_parents AS SELECT a.id, b.inventorytype AS type, a.parentid FROM inventory_parents AS a JOIN biotrackthc_inventory AS b on a.id=b.id JOIN biotrackthc_inventory AS c on a.parentid=c.id WHERE c.inventorytype > 12 ;

CREATE TEMPORARY TABLE tmp_inv_parents2 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents AS a JOIN tmp_inv_parents AS b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_inv_parents3 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents2 AS a JOIN tmp_inv_parents2 AS b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_inv_parents4 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents3 AS a JOIN tmp_inv_parents3 AS b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_inv_parents5 AS SELECT a.id, a.type, b.parentid FROM tmp_inv_parents4 AS a JOIN tmp_inv_parents4 AS b on a.parentid = b.id ;

CREATE TEMPORARY TABLE tmp_inv_parentall AS SELECT * from tmp_inv_parents UNION SELECT * from tmp_inv_parents2 UNION SELECT * from tmp_inv_parents3 UNION SELECT * from tmp_inv_parents4;

CREATE TABLE inventory_parentlot AS SELECT a.id, b.id AS parentlot FROM (select id, min(parentserial) as firstparentserial FROM (select a.id, a.parentid, b.idserial AS parentserial from tmp_inv_parentall AS a JOIN biotrackthc_inventory AS b on a.parentid = b.id ORDER BY a.id) GROUP BY id) AS a JOIN biotrackthc_inventory AS b on a.firstparentserial = b.idserial;

CREATE UNIQUE INDEX inventory_parentlot_id ON inventory_parentlot(id);

ANALYZE inventory_parentlot;

"""

c.executescript(query)

# Derived table: plant_summary
print("Creating plant_summary")
query = """
CREATE TEMPORARY TABLE plants_planted AS select plantid, min(created) as planted from biotrackthc_plantslog group by 1;
CREATE TEMPORARY TABLE plants_harvested AS select plantid, min(created) as planted from biotrackthc_plantslog where state = 1 group by 1;
CREATE TEMPORARY TABLE plants_cured AS select plantid, min(created) as planted from biotrackthc_plantslog where state = 2 group by 1;
CREATE TEMPORARY TABLE plants_harvestcollect AS select plantid, min(created) as firstharvest FROM (select plantid, created from biotrackthc_plantslog WHERE harvestcollect = 1) group by 1;

create table plant_summary as select a.id as plantid, a.strain, a.seed, a.location, a.orgid, a.deleted, a.state, a.cloneconverted, a.mother, a.seized, d.planted as date_planted, b.planted as date_total_harvest, c.planted as date_finalstate, e.firstharvest as date_firstharvest from biotrackthc_plants AS a 
left outer join plants_planted as d on a.id = d.plantid
left outer join plants_harvested as b on a.id = b.plantid
left outer join plants_cured as c on a.id = c.plantid
left outer join plants_harvestcollect as e on a.id = e.plantid;
"""
c.executescript(query)
getAll('plant_summary',c)

# Derived table: SUPPLY_CHAIN_SUMMARY
print("Creating supply_chain_summary...")
c.execute("DROP TABLE IF EXISTS supply_chain_summary")
query = "CREATE TABLE supply_chain_summary AS SELECT sales.inventoryid as inventoryid, inv.inventorytype as type, " + \
        sanitize('inv.strain') + ", " + sanitize('inv.productname') + ", coalesce(inv.usableweight, inv.weight) as usable_weight, " +\
        "sales.orgid AS salesorg, sales.location AS salesloc, tran.orgid AS procorg, tran.location AS proclog, prod.prodorg AS prodorg, parentlot.parentlot AS parentlot, time.first_seen AS date_wholesale,  " +\
        "sales.unitprice_avgretail, max(CAST(tran.saleprice AS decimal)/tran.weight, tran.unitprice) AS unitprice_wholesale, tran.weight AS weight_original, " +\
        " inv.remainingweight AS weight_remaining, pot.lab_license, pot.potency_CBD, pot.potency_THC, pot.potency_THCA, pot.potency_total " + \
        " FROM  (SELECT orgid, location, inventoryid, avg(price/weight) AS unitprice_avgretail FROM biotrackthc_dispensing WHERE deleted == 0 GROUP BY 1,2,3) as sales " +\
        "JOIN biotrackthc_inventorytransfers AS tran ON sales.inventoryid = tran.inventoryid AND tran.inbound_location = sales.location AND tran.inbound_orgid = sales.orgid AND tran.is_refund == 0 " +\
        "JOIN inventory_timestamps AS time ON sales.inventoryid = time.id AND sales.orgid = time.orgid AND sales.location = time.location " +\
        "LEFT OUTER JOIN biotrackthc_inventory AS inv ON inv.id = sales.inventoryid AND inv.location = sales.location AND inv.orgid = sales.orgid " +\
        "LEFT OUTER JOIN potency_summary AS pot ON pot.sample_id = inv.sample_id " +\
        "LEFT OUTER JOIN inventory_producer AS prod on prod.inventoryid = sales.inventoryid " +\
        "LEFT OUTER JOIN inventory_parentlot AS parentlot ON sales.inventoryid = parentlot.id"
        
        
print(query)
c.execute(query)
getAll('supply_chain_summary',c)

# Derived table: MANY_TO_MANY
print("Creating many_to_many from script...")
query = """
CREATE TEMPORARY TABLE tmp_plot_parents1 AS SELECT a.parentlot, b.parentid from (select parentlot from inventory_parentlot group by 1) as a join inventory_parents as b on a.parentlot=b.id;

CREATE TEMPORARY TABLE tmp_plot_parents2 AS SELECT a.parentlot, b.parentid from tmp_plot_parents1 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents3 AS SELECT a.parentlot, b.parentid from tmp_plot_parents2 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents4 AS SELECT a.parentlot, b.parentid from tmp_plot_parents3 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents5 AS SELECT a.parentlot, b.parentid from tmp_plot_parents4 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents6 AS SELECT a.parentlot, b.parentid from tmp_plot_parents5 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents7 AS SELECT a.parentlot, b.parentid from tmp_plot_parents6 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents8 AS SELECT a.parentlot, b.parentid from tmp_plot_parents7 as a join inventory_parents as b on a.parentid = b.id;

CREATE TEMPORARY TABLE tmp_plot_parents_all AS 
    SELECT * FROM tmp_plot_parents1
    UNION SELECT * FROM tmp_plot_parents2
    UNION SELECT * FROM tmp_plot_parents3
    UNION SELECT * FROM tmp_plot_parents4
    UNION SELECT * FROM tmp_plot_parents5
    UNION SELECT * FROM tmp_plot_parents6
    UNION SELECT * FROM tmp_plot_parents7
    UNION SELECT * FROM tmp_plot_parents8;
   
DROP TABLE IF EXISTS many_to_many;
CREATE TABLE many_to_many AS select a.parentlot, c.plantid from tmp_plot_parents_all as a join inventory_plants as c on a.parentid=c.id;
"""
c.executescript(query)
getAll('many_to_many',c)

# Derived table: producer_processor_summary
print("Creating producer_processor_summary")
c.execute("DROP TABLE IF EXISTS producer_processor_summary")
query = """
create table producer_processor_summary as select a.inventoryid, a.strain, a.weight, a.transactionid, a.location as prodloc,
a.orgid as prodorg, a.inventorytype, a.usableweight, a.description, a.saleprice, a.unitprice, a.is_refund, a.inbound_location as procloc,
a.inbound_orgid as procorg, d.first_seen as transfertime
from biotrackthc_inventorytransfers as a
join biotrackthc_locations as b on a.location = b.id
join biotrackthc_locations as c on a.inbound_location = c.id 
join inventory_timestamps as d on a.inbound_location = d.location and a.inventoryid = d.id
where b.producer = 1 and c.processor = 1;
"""
print(query)
c.executescript(query)
getAll('producer_processor_summary',c)

"""
# Finally, do the inventory log tables
# we want to reduce what we keep here to make these data a little smaller
baseLogQuery = "SELECT logid, log.id, log.sessiontime, " + \
               " weight, log.transactionid, plantid, log.orgid, log.location, remainingweight " + \
               ", requiresweighing, inventorytype,  log.deleted, created " + \
               ", iaction, lasttime, log.transactionid_original " + \
               "FROM biotrackthc_inventorylog AS log JOIN biotrackthc_locations AS loc ON log.location = loc.id AND log.orgid = loc.orgid "

producerOnlyQuery = baseLogQuery + "WHERE loc.producer == 1 AND loc.processor == 0 " + limit
processorOnlyQuery = baseLogQuery + "WHERE loc.producer == 0 AND loc.processor == 1 " + limit
bothQuery = baseLogQuery + "WHERE loc.producer == 1 AND loc.processor == 1 " + limit
retailerQuery = baseLogQuery + "WHERE loc.retail == 1 " + limit
querySave(producerOnlyQuery, c, 'inventorylog_produceronly')
querySave(processorOnlyQuery, c, 'inventorylog_processoronly')
querySave(bothQuery, c, 'inventorylog_both')
querySave(retailerQuery, c, 'inventorylog_retailer')
"""

# derived table: parentlot_time
print("Creating parentlot_time...")
c.execute("DROP TABLE IF EXISTS parentlot_time")
c.execute("CREATE TABLE parentlot_time AS select a.parentlot as parentlot, min(b.created) as first_child_seen from inventory_parentlot as a join biotrackthc_inventorylog as b on a.id = b.id group by 1")
getAll('parentlot_time',c)


# derived table: capacity_inventorylot
print("Creating capacity_inventorylot...")
c.execute("DROP TABLE IF EXISTS capacity_inventorylot")
c.execute("CREATE TEMPORARY TABLE first_seen AS select id, min(created) as first_seen from biotrackthc_inventorylog group by 1")
c.execute("CREATE TABLE capacity_inventorylot AS select a.inventoryid, a.proclog as procloc, a.type, a.usable_weight, a.weight_original, b.first_seen from supply_chain_summary as a JOIN first_seen as b on a.inventoryid = b.id")
getAll('capacity_inventorylot',c)

# derived table: capacity_parentlot
print("Creating capacity_parentlot...")
c.execute("DROP TABLE IF EXISTS capacity_parentlot")
c.execute("CREATE TEMPORARY TABLE initial_parentlot_weight as select id, location as procloc, inventorytype, min(created) as first_seen, max(remainingweight) as maxweight from biotrackthc_inventorylog where id in (select DISTINCT parentlot from supply_chain_summary) group by 1")
c.execute("CREATE TABLE capacity_parentlot AS select a.*, b.first_child_seen from initial_parentlot_weight as a JOIN parentlot_time as b ON a.id = b.parentlot")
getAll('capacity_parentlot',c)

# done with all tables, cleanup
conn.close()

    
